import csv
import time

import pymysql


class database:
    def __init__(self):
        self.DB_port = 'rm-m5ei41q6w870w5413ho.mysql.rds.aliyuncs.com'
        self.DB_user = 'visiter'
        self.DB_passwd = '@visiter123456'

    def Create_conn(self):
        conn = pymysql.connect(host=self.DB_port, user=self.DB_user, passwd=self.DB_passwd)
        return conn

    def Connect_table(self):
        conn = pymysql.connect(host=self.DB_port, user=self.DB_user, passwd=self.DB_passwd, db="didi_all")
        return conn

    def InitDB(self):
        """
        初始化数据库，清除所有数据
        :return:
        """
        try:
            conn = self.Create_conn()
            cursor = conn.cursor()  # 获取游标
            sql = 'drop database if exists didi_all'
            cursor.execute(sql)
            conn.commit()  # 提交
            cursor.close()  # 关闭游标
            conn.close()  # 退出连接
        except:
            self.Create_DB()
        self.Create_DB()

    def Create_DB(self):
        conn = self.Create_conn()
        cursor = conn.cursor()
        # 创建pythonBD数据库
        cursor.execute('CREATE DATABASE IF NOT EXISTS didi_all DEFAULT CHARSET utf8 COLLATE utf8_general_ci;')
        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游标
        # 创建信息表
        #
        sql = """
        create table if not exists dwv_haikou(
            order_id char (128) primary key,
            product_id char (64),
            city_id char (64),
            district char (64),
            county char (64),
            didi_type char (64),
            combo_type char (64),
            traffic_type char (64),
            passenger_count char (64),
            driver_product_id char (64),
            start_dest_distance char (64),
            arrive_time char (64),
            departure_time char (64),
            pre_total_fee char (64),
            normal_time char (64),
            bubble_trace_id char (128),
            product_1level char(64),
            dest_lng char (64),
            dest_lat char (64),
            starting_lng char (64),
            starting_lat char (64),
            year char (16),
            day char (16)
        );"""
        cursor.execute(sql)

    def add_data1(self, args):
        """
        添加 dwv_haikou
        :param args: 按照顺序分别是
        :return:
        """
        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游标
        sql = 'insert into didi_all.dwv_haikou value (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);'
        cursor.execute(sql, args)
        conn.commit()  # 提交
        cursor.close()  # 先关闭游标
        conn.close()  # 再关闭数据库连接

    def search_dep(self, data_mess):

        # import pandas as pd
        # import MySQLdb
        # mysql_cn = MySQLdb.connect(host='rm-m5ei41q6w870w5413ho.mysql.rds.aliyuncs.com',
        #                            port=3306, user='visiter',
        #                            passwd='@visiter123456', db='didi_all')
        #
        # sql = 'SELECT * FROM didi_all.dwv_haikou where departure_time like ' + '\'' + str(data_mess) + '\'' + ';'
        # df = pd.read_sql(sql, con=mysql_cn)
        # print(df)
        # mysql_cn.close()

        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游
        sql = 'SELECT * FROM didi_all.dwv_haikou where departure_time like ' + '\'' + str(data_mess) + '\'' + ';'
        print(sql)
        try:
            cursor.execute(sql)
            results = cursor.fetchall()
        except:
            print('error')
        cursor.close()  # 先关闭游标
        conn.close()  # 再关闭数据库连接
        return list(results)

    def get_col_name(self, table_name):
        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游标
        sql = "select column_name from information_schema.columns " \
              "where table_name="
        sql = sql + "'" + table_name + "'" + ' ORDER BY ordinal_position;'
        cursor.execute(sql)
        results = cursor.fetchall()
        cursor.close()  # 先关闭游标
        cursor.close()  # 先关闭游标
        conn.close()  # 再关闭数据库连接
        col_name = []
        for item in results:
            col_name.append(str(item[0]))
        return col_name


if __name__ == '__main__':
    db = database()

    start_tiem = time.time()
    fetch_data = db.search_dep('2017-05-01-%')
    end_time = time.time()
    print('timing-- ', (end_time - start_tiem))
    print(type)

    print('begin writing data')

    mess = ''

    col_head = db.get_col_name('dwv_haikou')
    for index in range(len(col_head) - 1):
        mess += str(col_head[index]) + ','
    mess += col_head[-1] + '\n'
    print(mess)

    for item in fetch_data:
        for index in range(len(item) - 1):
            mess += str(item[index]) + ','
        mess += item[-1] + '\n'

    print(mess)
    with open("test.txt", "w") as f:
        f.write(mess)

